package cn.iocoder.yudao.module.system.dal.mysql.examscore;

import cn.iocoder.yudao.framework.common.pojo.PageResult;
import cn.iocoder.yudao.framework.mybatis.core.mapper.BaseMapperX;
import cn.iocoder.yudao.framework.mybatis.core.query.LambdaQueryWrapperX;
import cn.iocoder.yudao.module.system.controller.admin.examscore.vo.ExamScorePageReqVO;
import cn.iocoder.yudao.module.system.dal.dataobject.examscore.ExamScoreDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 学生成绩 Mapper
 *
 * @author 芋道源码
 */
@Mapper
public interface ExamScoreMapper extends BaseMapperX<ExamScoreDO> {

    default PageResult<ExamScoreDO> selectPage(ExamScorePageReqVO reqVO) {
        return selectPage(reqVO, new LambdaQueryWrapperX<ExamScoreDO>()
                .eqIfPresent(ExamScoreDO::getExamId, reqVO.getExamId())
                .eqIfPresent(ExamScoreDO::getStudentId, reqVO.getStudentId())
                .eqIfPresent(ExamScoreDO::getClassId, reqVO.getClassId())
                .likeIfPresent(ExamScoreDO::getStudentNo, reqVO.getStudentNo())
                .likeIfPresent(ExamScoreDO::getStudentName, reqVO.getStudentName())
                .geIfPresent(ExamScoreDO::getTotalScore, reqVO.getMinTotalScore())
                .leIfPresent(ExamScoreDO::getTotalScore, reqVO.getMaxTotalScore())
                .betweenIfPresent(ExamScoreDO::getCreateTime, reqVO.getCreateTime())
                .orderByDesc(ExamScoreDO::getTotalScore, ExamScoreDO::getId));
    }

    default List<ExamScoreDO> selectList(ExamScorePageReqVO reqVO) {
        return selectList(new LambdaQueryWrapperX<ExamScoreDO>()
                .eqIfPresent(ExamScoreDO::getExamId, reqVO.getExamId())
                .eqIfPresent(ExamScoreDO::getStudentId, reqVO.getStudentId())
                .likeIfPresent(ExamScoreDO::getStudentNo, reqVO.getStudentNo())
                .likeIfPresent(ExamScoreDO::getStudentName, reqVO.getStudentName())
                .geIfPresent(ExamScoreDO::getTotalScore, reqVO.getMinTotalScore())
                .leIfPresent(ExamScoreDO::getTotalScore, reqVO.getMaxTotalScore())
                .betweenIfPresent(ExamScoreDO::getCreateTime, reqVO.getCreateTime())
                .orderByDesc(ExamScoreDO::getTotalScore, ExamScoreDO::getId));
    }

    default List<ExamScoreDO> selectListByExamId(Long examId) {
        return selectList(new LambdaQueryWrapperX<ExamScoreDO>()
                .eq(ExamScoreDO::getExamId, examId)
                .orderByDesc(ExamScoreDO::getTotalScore));
    }

    default List<ExamScoreDO> selectListByStudentId(Long studentId) {
        return selectList(new LambdaQueryWrapperX<ExamScoreDO>()
                .eq(ExamScoreDO::getStudentId, studentId)
                .orderByDesc(ExamScoreDO::getCreateTime));
    }

    default ExamScoreDO selectByExamIdAndStudentId(Long examId, Long studentId) {
        // 在存在重复数据的场景下，按创建时间和ID倒序仅取最新一条，避免 TooManyResultsException
        return selectOne(new LambdaQueryWrapperX<ExamScoreDO>()
                .eq(ExamScoreDO::getExamId, examId)
                .eq(ExamScoreDO::getStudentId, studentId)
                .orderByDesc(ExamScoreDO::getCreateTime)
                .orderByDesc(ExamScoreDO::getId)
                .last("LIMIT 1"));
    }

    default Long selectCountByExamId(Long examId) {
        return selectCount(ExamScoreDO::getExamId, examId);
    }

    /**
     * 计算班级排名
     */
    @Update("UPDATE system_exam_score s1 SET class_rank = (" +
            "SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "WHERE s2.exam_id = #{examId} AND s2.total_score > s1.total_score" +
            ") WHERE s1.exam_id = #{examId}")
    void updateClassRankByExamId(@Param("examId") Long examId);

    /**
     * 计算年级排名
     */
    @Update("UPDATE system_exam_score s1 SET grade_rank = (" +
            "SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "INNER JOIN system_exam_test t2 ON s2.exam_id = t2.id " +
            "INNER JOIN system_exam_test t1 ON s1.exam_id = t1.id " +
            "INNER JOIN system_exam_class c1 ON JSON_CONTAINS(t1.class_ids, CAST(c1.id AS JSON)) " +
            "INNER JOIN system_exam_class c2 ON JSON_CONTAINS(t2.class_ids, CAST(c2.id AS JSON)) " +
            "WHERE c1.grade = c2.grade AND s2.total_score > s1.total_score" +
            ") WHERE s1.exam_id = #{examId}")
    void updateGradeRankByExamId(@Param("examId") Long examId);

    /**
     * 获取考试成绩统计信息
     */
    @Select("SELECT " +
            "COUNT(*) as totalCount, " +
            "AVG(total_score) as avgScore, " +
            "MAX(total_score) as maxScore, " +
            "MIN(total_score) as minScore " +
            "FROM system_exam_score WHERE exam_id = #{examId}")
    ExamScoreStatisticsVO selectStatisticsByExamId(@Param("examId") Long examId);

    /**
     * 获取学生在指定考试中的班级排名
     */
    @Select("SELECT class_rank FROM system_exam_score " +
            "WHERE exam_id = #{examId} AND student_id = #{studentId}")
    Integer selectClassRankByExamIdAndStudentId(@Param("examId") Long examId, @Param("studentId") Long studentId);

    /**
     * 获取学生在指定考试中的年级排名
     */
    @Select("SELECT grade_rank FROM system_exam_score " +
            "WHERE exam_id = #{examId} AND student_id = #{studentId}")
    Integer selectGradeRankByExamIdAndStudentId(@Param("examId") Long examId, @Param("studentId") Long studentId);

    /**
     * 获取学生在指定考试中的科目班级排名
     */
    @Select("SELECT " +
            "CASE " +
            "WHEN #{subjectName} = '语文' THEN (SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "WHERE s2.exam_id = #{examId} AND s2.chinese_score > s1.chinese_score) " +
            "WHEN #{subjectName} = '数学' THEN (SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "WHERE s2.exam_id = #{examId} AND s2.math_score > s1.math_score) " +
            "WHEN #{subjectName} = '英语' THEN (SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "WHERE s2.exam_id = #{examId} AND s2.english_score > s1.english_score) " +
            "WHEN #{subjectName} = '物理' THEN (SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "WHERE s2.exam_id = #{examId} AND s2.physics_score > s1.physics_score) " +
            "WHEN #{subjectName} = '化学' THEN (SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "WHERE s2.exam_id = #{examId} AND s2.chemistry_score > s1.chemistry_score) " +
            "WHEN #{subjectName} = '生物' THEN (SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "WHERE s2.exam_id = #{examId} AND s2.biology_score > s1.biology_score) " +
            "WHEN #{subjectName} = '政治' THEN (SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "WHERE s2.exam_id = #{examId} AND s2.politics_score > s1.politics_score) " +
            "WHEN #{subjectName} = '历史' THEN (SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "WHERE s2.exam_id = #{examId} AND s2.history_score > s1.history_score) " +
            "WHEN #{subjectName} = '地理' THEN (SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "WHERE s2.exam_id = #{examId} AND s2.geography_score > s1.geography_score) " +
            "END as subjectClassRank " +
            "FROM system_exam_score s1 " +
            "WHERE s1.exam_id = #{examId} AND s1.student_id = #{studentId}")
    Integer selectSubjectClassRankByExamIdAndStudentId(@Param("examId") Long examId,
                                                      @Param("studentId") Long studentId,
                                                      @Param("subjectName") String subjectName);

    /**
     * 获取学生在指定考试中的科目年级排名
     */
    @Select("SELECT " +
            "CASE " +
            "WHEN #{subjectName} = '语文' THEN (SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "INNER JOIN system_exam_test t2 ON s2.exam_id = t2.id " +
            "INNER JOIN system_exam_class c2 ON JSON_CONTAINS(t2.class_ids, CAST(c2.id AS JSON)) " +
            "WHERE c2.grade = (SELECT c1.grade FROM system_exam_test t1 " +
            "INNER JOIN system_exam_class c1 ON JSON_CONTAINS(t1.class_ids, CAST(c1.id AS JSON)) WHERE t1.id = #{examId} LIMIT 1) " +
            "AND s2.chinese_score > s1.chinese_score) " +
            "WHEN #{subjectName} = '数学' THEN (SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "INNER JOIN system_exam_test t2 ON s2.exam_id = t2.id " +
            "INNER JOIN system_exam_class c2 ON JSON_CONTAINS(t2.class_ids, CAST(c2.id AS JSON)) " +
            "WHERE c2.grade = (SELECT c1.grade FROM system_exam_test t1 " +
            "INNER JOIN system_exam_class c1 ON JSON_CONTAINS(t1.class_ids, CAST(c1.id AS JSON)) WHERE t1.id = #{examId} LIMIT 1) " +
            "AND s2.math_score > s1.math_score) " +
            "WHEN #{subjectName} = '英语' THEN (SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "INNER JOIN system_exam_test t2 ON s2.exam_id = t2.id " +
            "INNER JOIN system_exam_class c2 ON JSON_CONTAINS(t2.class_ids, CAST(c2.id AS JSON)) " +
            "WHERE c2.grade = (SELECT c1.grade FROM system_exam_test t1 " +
            "INNER JOIN system_exam_class c1 ON JSON_CONTAINS(t1.class_ids, CAST(c1.id AS JSON)) WHERE t1.id = #{examId} LIMIT 1) " +
            "AND s2.english_score > s1.english_score) " +
            "WHEN #{subjectName} = '物理' THEN (SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "INNER JOIN system_exam_test t2 ON s2.exam_id = t2.id " +
            "INNER JOIN system_exam_class c2 ON JSON_CONTAINS(t2.class_ids, CAST(c2.id AS JSON)) " +
            "WHERE c2.grade = (SELECT c1.grade FROM system_exam_test t1 " +
            "INNER JOIN system_exam_class c1 ON JSON_CONTAINS(t1.class_ids, CAST(c1.id AS JSON)) WHERE t1.id = #{examId} LIMIT 1) " +
            "AND s2.physics_score > s1.physics_score) " +
            "WHEN #{subjectName} = '化学' THEN (SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "INNER JOIN system_exam_test t2 ON s2.exam_id = t2.id " +
            "INNER JOIN system_exam_class c2 ON JSON_CONTAINS(t2.class_ids, CAST(c2.id AS JSON)) " +
            "WHERE c2.grade = (SELECT c1.grade FROM system_exam_test t1 " +
            "INNER JOIN system_exam_class c1 ON JSON_CONTAINS(t1.class_ids, CAST(c1.id AS JSON)) WHERE t1.id = #{examId} LIMIT 1) " +
            "AND s2.chemistry_score > s1.chemistry_score) " +
            "WHEN #{subjectName} = '生物' THEN (SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "INNER JOIN system_exam_test t2 ON s2.exam_id = t2.id " +
            "INNER JOIN system_exam_class c2 ON JSON_CONTAINS(t2.class_ids, CAST(c2.id AS JSON)) " +
            "WHERE c2.grade = (SELECT c1.grade FROM system_exam_test t1 " +
            "INNER JOIN system_exam_class c1 ON JSON_CONTAINS(t1.class_ids, CAST(c1.id AS JSON)) WHERE t1.id = #{examId} LIMIT 1) " +
            "AND s2.biology_score > s1.biology_score) " +
            "WHEN #{subjectName} = '政治' THEN (SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "INNER JOIN system_exam_test t2 ON s2.exam_id = t2.id " +
            "INNER JOIN system_exam_class c2 ON JSON_CONTAINS(t2.class_ids, CAST(c2.id AS JSON)) " +
            "WHERE c2.grade = (SELECT c1.grade FROM system_exam_test t1 " +
            "INNER JOIN system_exam_class c1 ON JSON_CONTAINS(t1.class_ids, CAST(c1.id AS JSON)) WHERE t1.id = #{examId} LIMIT 1) " +
            "AND s2.politics_score > s1.politics_score) " +
            "WHEN #{subjectName} = '历史' THEN (SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "INNER JOIN system_exam_test t2 ON s2.exam_id = t2.id " +
            "INNER JOIN system_exam_class c2 ON JSON_CONTAINS(t2.class_ids, CAST(c2.id AS JSON)) " +
            "WHERE c2.grade = (SELECT c1.grade FROM system_exam_test t1 " +
            "INNER JOIN system_exam_class c1 ON JSON_CONTAINS(t1.class_ids, CAST(c1.id AS JSON)) WHERE t1.id = #{examId} LIMIT 1) " +
            "AND s2.history_score > s1.history_score) " +
            "WHEN #{subjectName} = '地理' THEN (SELECT COUNT(*) + 1 FROM system_exam_score s2 " +
            "INNER JOIN system_exam_test t2 ON s2.exam_id = t2.id " +
            "INNER JOIN system_exam_class c2 ON JSON_CONTAINS(t2.class_ids, CAST(c2.id AS JSON)) " +
            "WHERE c2.grade = (SELECT c1.grade FROM system_exam_test t1 " +
            "INNER JOIN system_exam_class c1 ON JSON_CONTAINS(t1.class_ids, CAST(c1.id AS JSON)) WHERE t1.id = #{examId} LIMIT 1) " +
            "AND s2.geography_score > s1.geography_score) " +
            "END as subjectGradeRank " +
            "FROM system_exam_score s1 " +
            "WHERE s1.exam_id = #{examId} AND s1.student_id = #{studentId}")
    Integer selectSubjectGradeRankByExamIdAndStudentId(@Param("examId") Long examId,
                                                      @Param("studentId") Long studentId,
                                                      @Param("subjectName") String subjectName);

    /**
     * 获取班级总人数
     */
    @Select("SELECT COUNT(*) FROM system_exam_score s " +
            "INNER JOIN system_exam_test t ON s.exam_id = t.id " +
            "WHERE s.exam_id = #{examId}")
    Integer selectClassTotalCountByExamId(@Param("examId") Long examId);

    /**
     * 获取年级总人数
     */
    @Select("SELECT COUNT(*) FROM system_exam_score s " +
            "INNER JOIN system_exam_test t ON s.exam_id = t.id " +
            "INNER JOIN system_exam_class c ON JSON_CONTAINS(t.class_ids, CAST(c.id AS JSON)) " +
            "WHERE c.grade = (SELECT c2.grade FROM system_exam_test t2 " +
            "INNER JOIN system_exam_class c2 ON JSON_CONTAINS(t2.class_ids, CAST(c2.id AS JSON)) WHERE t2.id = #{examId} LIMIT 1)")
    Integer selectGradeTotalCountByExamId(@Param("examId") Long examId);

    /**
     * 获取学生历史考试成绩（用于趋势分析）
     */
    @Select("SELECT s.*, t.exam_name, t.exam_date, t.exam_type " +
            "FROM system_exam_score s " +
            "INNER JOIN system_exam_test t ON s.exam_id = t.id " +
            "WHERE s.student_id = #{studentId} " +
            "ORDER BY t.exam_date DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> selectStudentScoreHistory(@Param("studentId") Long studentId, @Param("limit") Integer limit);

    /**
     * 获取年级分数分布统计
     */
    @Select("SELECT " +
            "CASE " +
            "WHEN s.total_score >= 700 THEN '700-750' " +
            "WHEN s.total_score >= 650 THEN '650-699' " +
            "WHEN s.total_score >= 600 THEN '600-649' " +
            "WHEN s.total_score >= 550 THEN '550-599' " +
            "WHEN s.total_score >= 500 THEN '500-549' " +
            "WHEN s.total_score >= 450 THEN '450-499' " +
            "ELSE '450以下' " +
            "END as scoreRange, " +
            "COUNT(*) as count " +
            "FROM system_exam_score s " +
            "INNER JOIN system_exam_test t ON s.exam_id = t.id " +
            "INNER JOIN system_exam_class c ON JSON_CONTAINS(t.class_ids, CAST(c.id AS JSON)) " +
            "WHERE c.grade = (SELECT c2.grade FROM system_exam_test t2 " +
            "INNER JOIN system_exam_class c2 ON JSON_CONTAINS(t2.class_ids, CAST(c2.id AS JSON)) WHERE t2.id = #{examId} LIMIT 1) " +
            "GROUP BY " +
            "CASE " +
            "WHEN s.total_score >= 700 THEN '700-750' " +
            "WHEN s.total_score >= 650 THEN '650-699' " +
            "WHEN s.total_score >= 600 THEN '600-649' " +
            "WHEN s.total_score >= 550 THEN '550-599' " +
            "WHEN s.total_score >= 500 THEN '500-549' " +
            "WHEN s.total_score >= 450 THEN '450-499' " +
            "ELSE '450以下' " +
            "END " +
            "ORDER BY MIN(s.total_score) DESC")
    List<Map<String, Object>> selectGradeScoreDistribution(@Param("examId") Long examId);

    /**
     * 获取年级统计信息（限定当前考试）
     */
    @Select("SELECT " +
            "COUNT(*) as totalStudents, " +
            "AVG(s.total_score) as avgScore, " +
            "MAX(s.total_score) as maxScore, " +
            "MIN(s.total_score) as minScore, " +
            "STDDEV(s.total_score) as standardDeviation " +
            "FROM system_exam_score s " +
            "WHERE s.exam_id = #{examId}")
    Map<String, Object> selectGradeStatistics(@Param("examId") Long examId);

    /**
     * 获取班级统计信息
     */
    @Select("SELECT " +
            "COUNT(*) as totalStudents, " +
            "AVG(s.total_score) as avgScore, " +
            "MAX(s.total_score) as maxScore, " +
            "MIN(s.total_score) as minScore, " +
            "STDDEV(s.total_score) as standardDeviation " +
            "FROM system_exam_score s " +
            "INNER JOIN system_exam_test t ON s.exam_id = t.id " +
            "WHERE s.exam_id = #{examId}")
    Map<String, Object> selectClassStatistics(@Param("examId") Long examId);

    // ==================== 教师看板相关查询方法 ====================

    /**
     * 获取班级成绩统计信息
     */
    @Select("SELECT " +
            "COUNT(*) as totalCount, " +
            "AVG(s.total_score) as avgScore, " +
            "MAX(s.total_score) as maxScore, " +
            "MIN(s.total_score) as minScore, " +
            "STDDEV(s.total_score) as standardDeviation " +
            "FROM system_exam_score s " +
            "INNER JOIN system_exam_test t ON s.exam_id = t.id " +
            "WHERE JSON_CONTAINS(t.class_ids, CAST(#{classId} AS JSON)) AND s.exam_id = #{examId}")
    Map<String, Object> selectClassScoreStatistics(@Param("classId") Long classId, @Param("examId") Long examId);

    /**
     * 获取班级各科成绩统计
     */
    @Select("SELECT " +
            "'语文' as subjectName, AVG(chinese_score) as avgScore, MAX(chinese_score) as maxScore, MIN(chinese_score) as minScore, " +
            "SUM(CASE WHEN chinese_score >= 90 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as passRate, " +
            "SUM(CASE WHEN chinese_score >= 120 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as excellentRate " +
            "FROM system_exam_score s INNER JOIN system_exam_test t ON s.exam_id = t.id " +
            "WHERE JSON_CONTAINS(t.class_ids, CAST(#{classId} AS JSON)) AND s.exam_id = #{examId} AND chinese_score IS NOT NULL " +
            "UNION ALL " +
            "SELECT '数学' as subjectName, AVG(math_score), MAX(math_score), MIN(math_score), " +
            "SUM(CASE WHEN math_score >= 90 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), " +
            "SUM(CASE WHEN math_score >= 120 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) " +
            "FROM system_exam_score s INNER JOIN system_exam_test t ON s.exam_id = t.id " +
            "WHERE JSON_CONTAINS(t.class_ids, CAST(#{classId} AS JSON)) AND s.exam_id = #{examId} AND math_score IS NOT NULL " +
            "UNION ALL " +
            "SELECT '英语' as subjectName, AVG(english_score), MAX(english_score), MIN(english_score), " +
            "SUM(CASE WHEN english_score >= 90 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), " +
            "SUM(CASE WHEN english_score >= 120 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) " +
            "FROM system_exam_score s INNER JOIN system_exam_test t ON s.exam_id = t.id " +
            "WHERE JSON_CONTAINS(t.class_ids, CAST(#{classId} AS JSON)) AND s.exam_id = #{examId} AND english_score IS NOT NULL")
    List<Map<String, Object>> selectClassSubjectStatistics(@Param("classId") Long classId, @Param("examId") Long examId);

    /**
     * 获取班级分数段分布
     */
    @Select("SELECT " +
            "CASE " +
            "WHEN s.total_score >= 650 THEN '650以上' " +
            "WHEN s.total_score >= 600 THEN '600-649' " +
            "WHEN s.total_score >= 550 THEN '550-599' " +
            "WHEN s.total_score >= 500 THEN '500-549' " +
            "WHEN s.total_score >= 450 THEN '450-499' " +
            "ELSE '450以下' " +
            "END as scoreRange, " +
            "COUNT(*) as count, " +
            "COUNT(*) * 100.0 / (SELECT COUNT(*) FROM system_exam_score s2 " +
            "INNER JOIN system_exam_test t2 ON s2.exam_id = t2.id " +
            "WHERE JSON_CONTAINS(t2.class_ids, CAST(#{classId} AS JSON)) AND s2.exam_id = #{examId}) as percentage " +
            "FROM system_exam_score s " +
            "INNER JOIN system_exam_test t ON s.exam_id = t.id " +
            "WHERE JSON_CONTAINS(t.class_ids, CAST(#{classId} AS JSON)) AND s.exam_id = #{examId} " +
            "GROUP BY " +
            "CASE " +
            "WHEN s.total_score >= 650 THEN '650以上' " +
            "WHEN s.total_score >= 600 THEN '600-649' " +
            "WHEN s.total_score >= 550 THEN '550-599' " +
            "WHEN s.total_score >= 500 THEN '500-549' " +
            "WHEN s.total_score >= 450 THEN '450-499' " +
            "ELSE '450以下' " +
            "END " +
            "ORDER BY MIN(s.total_score) DESC")
    List<Map<String, Object>> selectClassScoreDistribution(@Param("classId") Long classId, @Param("examId") Long examId);

    /**
     * 获取班级升学预测统计（基于特控线换算）
     */
    @Select("SELECT " +
            "SUM(CASE WHEN s.total_score >= 680 THEN 1 ELSE 0 END) as predicted985Count, " +
            "SUM(CASE WHEN s.total_score >= 680 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as predicted985Rate, " +
            "SUM(CASE WHEN s.total_score >= 650 THEN 1 ELSE 0 END) as predicted211Count, " +
            "SUM(CASE WHEN s.total_score >= 650 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as predicted211Rate, " +
            "SUM(CASE WHEN s.total_score >= 620 THEN 1 ELSE 0 END) as predictedFirstTierCount, " +
            "SUM(CASE WHEN s.total_score >= 620 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as predictedFirstTierRate, " +
            "SUM(CASE WHEN s.total_score >= 550 THEN 1 ELSE 0 END) as predictedSecondTierCount, " +
            "SUM(CASE WHEN s.total_score >= 550 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as predictedSecondTierRate, " +
            "SUM(CASE WHEN s.total_score >= 400 THEN 1 ELSE 0 END) as predictedSpecialtyCount, " +
            "SUM(CASE WHEN s.total_score >= 400 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as predictedSpecialtyRate, " +
            "SUM(CASE WHEN s.total_score < 400 THEN 1 ELSE 0 END) as belowLineCount, " +
            "SUM(CASE WHEN s.total_score < 400 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as belowLineRate " +
            "FROM system_exam_score s " +
            "INNER JOIN system_exam_test t ON s.exam_id = t.id " +
            "WHERE JSON_CONTAINS(t.class_ids, CAST(#{classId} AS JSON)) AND s.exam_id = #{examId}")
    Map<String, Object> selectClassAdmissionPrediction(@Param("classId") Long classId, @Param("examId") Long examId);

    /**
     * 获取班级前N名学生
     */
    @Select("SELECT s.student_id, s.student_name, s.student_no, s.class_name, s.total_score, s.class_rank, s.grade_rank, " +
            "s.subject_combination, s.first_subject, s.second_subject, s.exam_type, " +
            "s.total_score_assigned, s.combination_rank, s.school_rank, s.total_union_rank, " +
            "CASE " +
            "WHEN s.total_score >= 680 THEN '985' " +
            "WHEN s.total_score >= 650 THEN '211' " +
            "WHEN s.total_score >= 620 THEN '一本' " +
            "WHEN s.total_score >= 550 THEN '二本' " +
            "ELSE '专科' " +
            "END as predictedAdmissionLevel " +
            "FROM system_exam_score s " +
            "WHERE s.class_id = #{classId} AND s.exam_id = #{examId} " +
            "ORDER BY s.total_score DESC " +
            "LIMIT #{topN}")
    List<Map<String, Object>> selectClassTopStudents(@Param("classId") Long classId,
                                                     @Param("examId") Long examId,
                                                     @Param("topN") Integer topN);

    /**
     * 获取班级所有学生排名数据
     */
    @Select("SELECT s.student_id, s.student_name, s.student_no, s.class_name, s.total_score, s.class_rank, s.grade_rank, " +
             "s.subject_combination, s.first_subject, s.second_subject, s.exam_type, " +
             "s.total_score_assigned, s.combination_rank, s.school_rank, s.total_union_rank, " +
             "CASE " +
             "WHEN s.total_score >= #{score985} THEN '985' " +
             "WHEN s.total_score >= #{score211} THEN '211' " +
             "WHEN s.total_score >= #{scoreFirstTier} THEN '一本' " +
             "WHEN s.total_score >= #{scoreSecondTier} THEN '二本' " +
             "ELSE '专科' " +
             "END as predictedAdmissionLevel " +
             "FROM system_exam_score s " +
             "WHERE s.class_id = #{classId} AND s.exam_id = #{examId} " +
             "ORDER BY s.total_score DESC")
    List<Map<String, Object>> selectClassAllStudents(@Param("classId") Long classId,
                                                     @Param("examId") Long examId,
                                                     @Param("score985") Integer score985,
                                                     @Param("score211") Integer score211,
                                                     @Param("scoreFirstTier") Integer scoreFirstTier,
                                                     @Param("scoreSecondTier") Integer scoreSecondTier);

    /**
     * 查询临界学生（在分数线附近的学生）- 使用动态分数线配置
     */
    @Select("<script>" +
            "SELECT s.student_id, s.student_name, s.student_no, s.total_score, " +
            "CASE " +
            "WHEN s.total_score BETWEEN (#{score985} - #{range}) AND (#{score985} + #{range}) THEN '985临界' " +
            "WHEN s.total_score BETWEEN (#{score211} - #{range}) AND (#{score211} + #{range}) THEN '211临界' " +
            "WHEN s.total_score BETWEEN (#{scoreFirstTier} - #{range}) AND (#{scoreFirstTier} + #{range}) THEN '一本临界' " +
            "WHEN s.total_score BETWEEN (#{scoreSecondTier} - #{range}) AND (#{scoreSecondTier} + #{range}) THEN '二本临界' " +
            "WHEN s.total_score BETWEEN (#{scoreUndergraduate} - #{undergraduateRange}) AND (#{scoreUndergraduate} + #{range}) THEN '本科临界' " +
            "END as criticalType, " +
            "CASE " +
            "WHEN s.total_score BETWEEN (#{score985} - #{range}) AND (#{score985} + #{range}) THEN #{score985} - s.total_score " +
            "WHEN s.total_score BETWEEN (#{score211} - #{range}) AND (#{score211} + #{range}) THEN #{score211} - s.total_score " +
            "WHEN s.total_score BETWEEN (#{scoreFirstTier} - #{range}) AND (#{scoreFirstTier} + #{range}) THEN #{scoreFirstTier} - s.total_score " +
            "WHEN s.total_score BETWEEN (#{scoreSecondTier} - #{range}) AND (#{scoreSecondTier} + #{range}) THEN #{scoreSecondTier} - s.total_score " +
            "WHEN s.total_score BETWEEN (#{scoreUndergraduate} - #{undergraduateRange}) AND (#{scoreUndergraduate} + #{range}) THEN #{scoreUndergraduate} - s.total_score " +
            "END as scoreDiff " +
            "FROM system_exam_score s " +
            "WHERE s.exam_id = #{examId} " +
            "<if test='classId != null'>" +
            "AND s.class_id = #{classId} " +
            "</if>" +
            "AND (s.total_score BETWEEN (#{score985} - #{range}) AND (#{score985} + #{range}) " +
            "OR s.total_score BETWEEN (#{score211} - #{range}) AND (#{score211} + #{range}) " +
            "OR s.total_score BETWEEN (#{scoreFirstTier} - #{range}) AND (#{scoreFirstTier} + #{range}) " +
            "OR s.total_score BETWEEN (#{scoreSecondTier} - #{range}) AND (#{scoreSecondTier} + #{range}) " +
            "OR s.total_score BETWEEN (#{scoreUndergraduate} - #{undergraduateRange}) AND (#{scoreUndergraduate} + #{range})) " +
            "ORDER BY s.total_score DESC " +
            "</script>")
    List<Map<String, Object>> selectCriticalStudents(@Param("classId") Long classId,
                                                     @Param("examId") Long examId,
                                                     @Param("range") Integer range,
                                                     @Param("score985") Integer score985,
                                                     @Param("score211") Integer score211,
                                                     @Param("scoreFirstTier") Integer scoreFirstTier,
                                                     @Param("scoreSecondTier") Integer scoreSecondTier,
                                                     @Param("scoreUndergraduate") Integer scoreUndergraduate,
                                                     @Param("undergraduateRange") Integer undergraduateRange);

    /**
     * 获取班级进步最大的学生
     */
    @Select("SELECT s1.student_id, s1.student_name, s1.student_no, " +
            "s1.total_score as currentScore, s2.total_score as previousScore, " +
            "(s1.total_score - s2.total_score) as progressScore, " +
            "(s2.class_rank - s1.class_rank) as rankImprovement " +
            "FROM system_exam_score s1 " +
            "LEFT JOIN system_exam_score s2 ON s1.student_id = s2.student_id AND s2.class_id = #{classId} " +
            "WHERE s1.class_id = #{classId} AND s1.exam_id = #{currentExamId} " +
            "AND s2.exam_id = #{previousExamId} " +
            "AND (s1.total_score - s2.total_score) > 0 " +
            "ORDER BY (s1.total_score - s2.total_score) DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> selectMostImprovedStudents(@Param("classId") Long classId,
                                                         @Param("currentExamId") Long currentExamId,
                                                         @Param("previousExamId") Long previousExamId,
                                                         @Param("limit") Integer limit);

    /**
     * 获取需要关注的学生（成绩下滑或排名靠后）
     */
    @Select("SELECT s1.student_id, s1.student_name, s1.student_no, " +
            "s1.total_score as currentScore, s2.total_score as previousScore, " +
            "(s1.total_score - s2.total_score) as scoreChange, " +
            "(s1.class_rank - s2.class_rank) as rankChange, " +
            "s1.subject_combination, s1.first_subject, s1.second_subject, s1.exam_type, " +
            "s1.total_score_assigned as current_score_assigned, s1.total_union_rank, " +
            "CASE " +
            "WHEN (s1.total_score - s2.total_score) < -20 THEN '成绩大幅下滑' " +
            "WHEN (s1.total_score - s2.total_score) < -10 THEN '成绩下滑' " +
            "WHEN s1.class_rank > (SELECT COUNT(*) * 0.8 FROM system_exam_score s3 " +
            "WHERE s3.class_id = #{classId} AND s3.exam_id = #{currentExamId}) THEN '排名靠后' " +
            "ELSE '其他' " +
            "END as attentionReason " +
            "FROM system_exam_score s1 " +
            "LEFT JOIN system_exam_score s2 ON s1.student_id = s2.student_id AND s2.class_id = #{classId} " +
            "WHERE s1.class_id = #{classId} AND s1.exam_id = #{currentExamId} " +
            "AND s2.exam_id = #{previousExamId} " +
            "AND ((s1.total_score - s2.total_score) < -10 " +
            "OR s1.class_rank > (SELECT COUNT(*) * 0.8 FROM system_exam_score s4 " +
            "WHERE s4.class_id = #{classId} AND s4.exam_id = #{currentExamId})) " +
            "ORDER BY (s1.total_score - s2.total_score) ASC")
    List<Map<String, Object>> selectAttentionStudents(@Param("classId") Long classId,
                                                      @Param("currentExamId") Long currentExamId,
                                                      @Param("previousExamId") Long previousExamId);

    /**
     * 获取班级在年级中的排名
     */
    @Select("SELECT COUNT(*) + 1 as class_rank " +
            "FROM (" +
            "SELECT JSON_UNQUOTE(JSON_EXTRACT(t.class_ids, '$[0]')) as class_id, AVG(s.total_score) as class_avg_score " +
            "FROM system_exam_score s " +
            "INNER JOIN system_exam_test t ON s.exam_id = t.id " +
            "INNER JOIN system_exam_class c ON JSON_CONTAINS(t.class_ids, CAST(c.id AS JSON)) " +
            "WHERE s.exam_id = #{examId} " +
            "AND c.grade = (SELECT c2.grade FROM system_exam_class c2 WHERE c2.id = #{classId}) " +
            "GROUP BY JSON_UNQUOTE(JSON_EXTRACT(t.class_ids, '$[0]')) " +
            ") other_classes " +
            "WHERE other_classes.class_avg_score > (" +
            "SELECT AVG(s2.total_score) " +
            "FROM system_exam_score s2 " +
            "INNER JOIN system_exam_test t2 ON s2.exam_id = t2.id " +
            "WHERE JSON_CONTAINS(t2.class_ids, CAST(#{classId} AS JSON)) AND s2.exam_id = #{examId}" +
            ")")
    Integer selectClassRankInGrade(@Param("classId") Long classId, @Param("examId") Long examId);

    /**
     * 获取年级班级总数
     */
    @Select("SELECT COUNT(DISTINCT c.id) " +
            "FROM system_exam_test t " +
            "INNER JOIN system_exam_class c ON JSON_CONTAINS(t.class_ids, CAST(c.id AS JSON)) " +
            "WHERE t.id = #{examId} " +
            "AND c.grade = (SELECT c2.grade FROM system_exam_class c2 WHERE c2.id = #{classId})")
    Integer selectTotalClassesInGrade(@Param("classId") Long classId, @Param("examId") Long examId);

    /**
     * 获取班级历史考试升学趋势 - 使用动态分数线配置
     */
    @Select("SELECT t.exam_name, t.exam_date, " +
            "AVG(s.total_score) as classAvgScore, " +
            "SUM(CASE WHEN s.total_score >= #{score985} THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as rate985, " +
            "SUM(CASE WHEN s.total_score >= #{score211} THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as rate211, " +
            "SUM(CASE WHEN s.total_score >= #{scoreFirstTier} THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as firstTierRate " +
            "FROM system_exam_score s " +
            "INNER JOIN system_exam_test t ON s.exam_id = t.id " +
            "WHERE JSON_CONTAINS(t.class_ids, CAST(#{classId} AS JSON)) " +
            "GROUP BY t.id, t.exam_name, t.exam_date " +
            "ORDER BY t.exam_date DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> selectClassAdmissionTrend(@Param("classId") Long classId, 
                                                       @Param("limit") Integer limit,
                                                       @Param("score985") Integer score985,
                                                       @Param("score211") Integer score211,
                                                       @Param("scoreFirstTier") Integer scoreFirstTier);

    /**
     * 获取班级与其他班级的对比数据
     */
    @Select("SELECT ranked_data.class_name, " +
            "ranked_data.classAvgScore, " +
            "ranked_data.firstTierRate, " +
            "@avg_rank := @avg_rank + 1 as avgScoreRank, " +
            "(SELECT COUNT(*) + 1 FROM (" +
            "SELECT JSON_UNQUOTE(JSON_EXTRACT(t2.class_ids, '$[0]')) as class_id, SUM(CASE WHEN s2.total_score >= #{scoreFirstTier} THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as rate " +
            "FROM system_exam_score s2 " +
            "INNER JOIN system_exam_test t2 ON s2.exam_id = t2.id " +
            "INNER JOIN system_exam_class c2 ON JSON_CONTAINS(t2.class_ids, CAST(c2.id AS JSON)) " +
            "WHERE s2.exam_id = #{examId} " +
            "AND c2.grade = (SELECT c3.grade FROM system_exam_class c3 WHERE c3.id = #{classId}) " +
            "GROUP BY JSON_UNQUOTE(JSON_EXTRACT(t2.class_ids, '$[0]')) " +
            ") other_rates WHERE other_rates.rate > ranked_data.firstTierRate) as firstTierRateRank " +
            "FROM (" +
            "SELECT c.class_name, JSON_UNQUOTE(JSON_EXTRACT(t.class_ids, '$[0]')) as class_id, " +
            "AVG(s.total_score) as classAvgScore, " +
            "SUM(CASE WHEN s.total_score >= #{scoreFirstTier} THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as firstTierRate " +
            "FROM system_exam_score s " +
            "INNER JOIN system_exam_test t ON s.exam_id = t.id " +
            "INNER JOIN system_exam_class c ON JSON_CONTAINS(t.class_ids, CAST(c.id AS JSON)) " +
            "WHERE s.exam_id = #{examId} " +
            "AND c.grade = (SELECT c2.grade FROM system_exam_class c2 WHERE c2.id = #{classId}) " +
            "GROUP BY JSON_UNQUOTE(JSON_EXTRACT(t.class_ids, '$[0]')), c.class_name " +
            "ORDER BY AVG(s.total_score) DESC " +
            ") ranked_data " +
            "CROSS JOIN (SELECT @avg_rank := 0) r")
    List<Map<String, Object>> selectClassComparison(@Param("classId") Long classId, 
                                                   @Param("examId") Long examId,
                                                   @Param("scoreFirstTier") Integer scoreFirstTier);

    /**
     * 获取学生详细升学信息
     */
    @Select("SELECT s.student_id, s.student_name, s.student_no, s.total_score, " +
            "s.chinese_score, s.math_score, s.english_score, s.physics_score, s.chemistry_score, s.biology_score, " +
            "s.class_rank, s.grade_rank, " +
            "CASE " +
            "WHEN s.total_score >= #{score985} THEN '985' " +
            "WHEN s.total_score >= #{score211} THEN '211' " +
            "WHEN s.total_score >= #{scoreFirstTier} THEN '一本' " +
            "WHEN s.total_score >= #{scoreSecondTier} THEN '二本' " +
            "WHEN s.total_score >= #{scoreSpecialist} THEN '专科' " +
            "ELSE '未达线' " +
            "END as predictedAdmissionLevel, " +
            "(s.total_score * 1.1) as predictedGaokaoScore " +
            "FROM system_exam_score s " +
            "INNER JOIN system_exam_test t ON s.exam_id = t.id " +
            "WHERE JSON_CONTAINS(t.class_ids, CAST(#{classId} AS JSON)) AND s.exam_id = #{examId} " +
            "ORDER BY s.total_score DESC")
    List<Map<String, Object>> selectStudentAdmissionDetails(@Param("classId") Long classId, 
                                                           @Param("examId") Long examId,
                                                           @Param("score985") Integer score985,
                                                           @Param("score211") Integer score211,
                                                           @Param("scoreFirstTier") Integer scoreFirstTier,
                                                           @Param("scoreSecondTier") Integer scoreSecondTier,
                                                           @Param("scoreSpecialist") Integer scoreSpecialist);

    /**
     * 根据班级ID查询考试成绩
     */
    default List<ExamScoreDO> selectByExamIdAndClassId(Long examId, Long classId) {
        return selectList(new LambdaQueryWrapperX<ExamScoreDO>()
                .eq(ExamScoreDO::getExamId, examId)
                .eq(ExamScoreDO::getClassId, classId)
                .orderByDesc(ExamScoreDO::getTotalScore));
    }

    /**
     * 根据选科组合查询考试成绩
     */
    default List<ExamScoreDO> selectByExamIdAndSubjectCombination(Long examId, String subjectCombination) {
        return selectList(new LambdaQueryWrapperX<ExamScoreDO>()
                .eq(ExamScoreDO::getExamId, examId)
                .eq(ExamScoreDO::getSubjectCombination, subjectCombination)
                .orderByDesc(ExamScoreDO::getTotalScore));
    }

    /**
     * 根据考试ID删除所有成绩数据（用于清除导入的数据）
     */
    default ExamScoreDO selectByStudentNoAndDeptId(String studentNo, Long deptId) {
        return selectOne(new LambdaQueryWrapperX<ExamScoreDO>()
                .eq(ExamScoreDO::getStudentNo, studentNo)
                .eq(ExamScoreDO::getDeptId, deptId)
                .last("LIMIT 1"));
    }

    default int deleteByExamId(Long examId) {
        return delete(new LambdaQueryWrapperX<ExamScoreDO>()
                .eq(ExamScoreDO::getExamId, examId));
    }
    
    /**
     * 根据导入批次ID删除成绩数据（用于按批次清除导入的数据）
     */
    default int deleteByImportBatchId(String importBatchId) {
        return delete(new LambdaQueryWrapperX<ExamScoreDO>()
                .eq(ExamScoreDO::getImportBatchId, importBatchId));
    }
    
    /**
     * 根据导入批次ID查询成绩数量
     */
    default Long selectCountByImportBatchId(String importBatchId) {
        return selectCount(ExamScoreDO::getImportBatchId, importBatchId);
    }
    
    /**
     * 根据导入批次ID查询成绩列表
     */
    default List<ExamScoreDO> selectListByImportBatchId(String importBatchId) {
        return selectList(new LambdaQueryWrapperX<ExamScoreDO>()
                .eq(ExamScoreDO::getImportBatchId, importBatchId)
                .orderByDesc(ExamScoreDO::getCreateTime));
    }

    /**
     * 批量查询考试成绩（根据考试ID和学生ID列表）
     */
    default List<ExamScoreDO> selectByExamIdAndStudentIds(Long examId, List<Long> studentIds) {
        if (studentIds == null || studentIds.isEmpty()) {
            return new ArrayList<>();
        }
        return selectList(new LambdaQueryWrapperX<ExamScoreDO>()
                .eq(ExamScoreDO::getExamId, examId)
                .in(ExamScoreDO::getStudentId, studentIds));
    }

    /**
     * 批量更新成绩记录（性能优化）
     * 使用MyBatis-Plus的批量更新功能，每批500条
     */
    default int updateBatchById(List<ExamScoreDO> scores) {
        if (scores == null || scores.isEmpty()) {
            return 0;
        }
        
        int totalUpdated = 0;
        int batchSize = 500;
        
        for (int i = 0; i < scores.size(); i += batchSize) {
            int endIndex = Math.min(i + batchSize, scores.size());
            List<ExamScoreDO> batch = scores.subList(i, endIndex);
            
            // 使用BaseMapper的updateById方法逐个更新（在事务中会被优化）
            for (ExamScoreDO score : batch) {
                updateById(score);
                totalUpdated++;
            }
        }
        
        return totalUpdated;
    }

    /**
     * 考试成绩统计信息VO
     */
    class ExamScoreStatisticsVO {
        private Long totalCount;
        private Double avgScore;
        private Double maxScore;
        private Double minScore;

        // getters and setters
        public Long getTotalCount() { return totalCount; }
        public void setTotalCount(Long totalCount) { this.totalCount = totalCount; }
        public Double getAvgScore() { return avgScore; }
        public void setAvgScore(Double avgScore) { this.avgScore = avgScore; }
        public Double getMaxScore() { return maxScore; }
        public void setMaxScore(Double maxScore) { this.maxScore = maxScore; }
        public Double getMinScore() { return minScore; }
        public void setMinScore(Double minScore) { this.minScore = minScore; }
    }

}
